﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using QLGT_2.Models;
using Kendo.Mvc.UI;
using Kendo.Mvc.Extensions;
using System.Data.Entity;
using System.Data.Entity.Validation;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace QLGT_2.Controllers
{
    public class TimKiemController : Controller
    {
        QLGTContext db = new QLGTContext();

        // GET: TimKiem
        public ActionResult Index()
        {
            return View();
        }

        #region TKGiaoTrinh
        public ActionResult TKGiaoTrinh()
        {
            if (Request.IsAjaxRequest())
                return PartialView();
            return View();
        }

        [HttpPost]
        [ActionName("TKGiaoTrinh")]
        public ActionResult POST_TKGiaoTrinh(tbl_GiaoTrinh item)
        {
            var dsGiaoTrinh = from a in db.tbl_GiaoTrinhs
                                 join b in db.tbl_GiaoTrinh_Nams on a.MaGiaoTrinh equals b.MaGiaoTrinh
                                 join c in db.tbl_GiaoTrinh_TacGias on a.MaGiaoTrinh equals c.MaGiaoTrinh
                                 join d in db.tbl_TacGias on c.MaTacGia equals d.MaTacGia
                                 join e in db.tbl_HocHams on d.MaHocHam equals e.MaHocHam
                                 join f in db.tbl_HocVis on d.MaHocVi equals f.MaHocVi
                                 join g in db.tbl_NhaXuatBans on a.MaNXB equals g.MaNXB
                                 select new kqtkGiaoTrinh
                                 {
                                     MaGiaoTrinh = a.MaGiaoTrinh,
                                     TenGiaoTrinh = a.TenGiaoTrinh,
                                     HieuDinh = a.HieuDinh,
                                     MaNXB = a.MaNXB,
                                     TenNXB = g.TenNXB,
                                     GiaNiemYet = b.GiaNiemYet,
                                     MaTacGia = c.MaTacGia,
                                     TenTacGia = d.TenTacGia,
                                     MaHocHam = d.MaHocHam,
                                     HocHam = e.HocHam,
                                     VTHocHam = e.VietTat,
                                     MaHocVi = d.MaHocVi,
                                     HocVi = f.HocVi,
                                     VTHocVi = f.VietTat
                                 };
            var dsGopMa = dsGiaoTrinh.GroupBy(m => m.MaGiaoTrinh).Select(m => m.FirstOrDefault()).ToList();
            List<kqtkGiaoTrinh> dsCuoi = new List<kqtkGiaoTrinh>();
            foreach (var a in dsGopMa)
            {
                string tacGia = "";
                foreach (var b in dsGiaoTrinh)
                {
                    if (b.MaGiaoTrinh.Trim() == a.MaGiaoTrinh.Trim())
                    {
                        if (b.HocHam != "Không có" && b.VTHocHam != null)
                        {
                            tacGia += b.VTHocHam + ". TS. " + b.TenTacGia+"; ";
                        }
                        else if (b.HocVi != "Không có" && b.VTHocVi != null)
                        {
                            tacGia += b.VTHocVi + ". " + b.TenTacGia + "; ";		 
                        }
                        else
                        {
                            tacGia += b.TenTacGia+ "; ";
                        }
                    }
                }
                tacGia = tacGia.Substring(0, tacGia.Length - 2).Trim();
                dsCuoi.Add(new kqtkGiaoTrinh()
                {
                    MaGiaoTrinh = a.MaGiaoTrinh,
                    TenGiaoTrinh = a.TenGiaoTrinh,
                    HieuDinh = a.HieuDinh,
                    TenNXB = a.TenNXB,
                    GiaNiemYet = a.GiaNiemYet,
                    TenTacGia = tacGia
                });
            }

            if (!String.IsNullOrEmpty(item.MaGiaoTrinh))
            {
                dsCuoi = dsCuoi.Where(m => m.MaGiaoTrinh.Contains(item.MaGiaoTrinh)).ToList();
            }
            if (!String.IsNullOrEmpty(item.TenGiaoTrinh))
            {
                dsCuoi = dsCuoi.Where(m => m.TenGiaoTrinh.Contains(item.TenGiaoTrinh)).ToList();
            }
            if (!String.IsNullOrEmpty(Request.Form["txtNhaXuatBan"].Trim()))
            {
                string nxb = Request.Form["txtNhaXuatBan"].Trim();
                dsCuoi = dsCuoi.Where(m => m.TenNXB.Contains(nxb)).ToList();
            }
            if (!String.IsNullOrEmpty(Request.Form["txtTacGia"].Trim()))
            {
                string TacGia = Request.Form["txtTacGia"].Trim();
                dsCuoi = dsCuoi.Where(m => m.TenTacGia.Contains(TacGia)).ToList();
            }
            if (!String.IsNullOrEmpty(Request.Form["txtNgay"].Trim()))
            {
                int ngay = int.Parse(Request.Form["txtNgay"].Trim());
                dsCuoi = dsCuoi.Where(m => m.HieuDinh.Value.Day.Equals(ngay)).ToList();
            }
            if (!String.IsNullOrEmpty(Request.Form["txtThang"].Trim()))
            {
                int thang = int.Parse(Request.Form["txtThang"].Trim());
                dsCuoi = dsCuoi.Where(m => m.HieuDinh.Value.Month.Equals(thang)).ToList();
            }
            if (!String.IsNullOrEmpty(Request.Form["txtNam"].Trim()))
            {
                int nam = int.Parse(Request.Form["txtNam"].Trim());
                dsCuoi = dsCuoi.Where(m => m.HieuDinh.Value.Year.Equals(nam)).ToList();
            }
            return PartialView("_KQTKGiaoTrinh", dsCuoi);
        }
        public ActionResult ExportGiaoTrinh(string contentType, string base64, string fileName)
        {
            var fileContents = Convert.FromBase64String(base64);

            return File(fileContents, contentType, fileName);
        }
        #endregion

        #region TKDaoTao
        public ActionResult TKDaoTao()
        {
            if (Request.IsAjaxRequest())
                return PartialView();
            return View();
        }

        [HttpPost]
        [ActionName("TKDaoTao")]
        public ActionResult POST_TKDaoTao(tbl_DaoTao item)
        {
            var dsDaoTao = from a in db.tbl_DaoTaos
                              join b in db.tbl_Nganhs on a.MaNganh equals b.MaNganh
                              join c in db.tbl_MonHocs on a.MaMonHoc equals c.MaMonHoc
                              join d in db.tbl_Khoas on c.MaKhoa equals d.MaKhoa
                              join e in db.tbl_GiaoTrinhs on c.MaGiaoTrinh equals e.MaGiaoTrinh
                              select new kqtkDaoTao
                              {
                                  MaNganh = b.MaNganh,
                                  TenNganh = b.TenNganh,
                                  MaMonHoc = c.MaMonHoc,
                                  TenMonHoc = c.TenMonHoc,
                                  TenKhoa = d.TenKhoa,
                                  TenGiaoTrinh = e.TenGiaoTrinh,
                                  NamHoc = a.NamHoc,
                                  KyHoc = a.KyHoc,
                                  SoTinChi = a.SoTinChi
                              };
            
            if (!String.IsNullOrEmpty(Request.Form["txtTenNganh"].Trim()))
            {
                string nganh = Request.Form["txtTenNganh"].Trim();
                dsDaoTao = dsDaoTao.Where(m => m.TenNganh.Contains(nganh));
            }
            if (!String.IsNullOrEmpty(Request.Form["txtTenMonHoc"].Trim()))
            {
                string mon = Request.Form["txtTenMonHoc"].Trim();
                dsDaoTao = dsDaoTao.Where(m => m.TenMonHoc.Contains(mon));
            }
            if (!String.IsNullOrEmpty(Request.Form["txtTenKhoa"].Trim()))
            {
                string khoa = Request.Form["txtTenKhoa"].Trim();
                dsDaoTao = dsDaoTao.Where(m => m.TenKhoa.Contains(khoa));
            }
            if (!String.IsNullOrEmpty(Request.Form["txtTenGiaoTrinh"].Trim()))
            {
                string giaotrinh = Request.Form["txtTenGiaoTrinh"].Trim();
                dsDaoTao = dsDaoTao.Where(m => m.TenGiaoTrinh.Contains(giaotrinh));
            }
            if (!String.IsNullOrEmpty(Request.Form["listNam"].Trim()))
            {
                int nam = int.Parse(Request.Form["listNam"].Trim());
                dsDaoTao = dsDaoTao.Where(m => m.NamHoc.Equals(nam));
            }
            if (!String.IsNullOrEmpty(Request.Form["listKy"].Trim()))
            {
                int ky = int.Parse(Request.Form["listKy"].Trim());
                dsDaoTao = dsDaoTao.Where(m => m.KyHoc.Equals(ky));
            }
            if (!String.IsNullOrEmpty(Request.Form["listTinChi"].Trim()))
            {
                int tinchi = int.Parse(Request.Form["listTinChi"].Trim());
                dsDaoTao = dsDaoTao.Where(m => m.NamHoc.Equals(tinchi));
            }


            return PartialView("_KQTKDaoTao", dsDaoTao);
        }
        //public ActionResult ExportHighSchool(string contentType, string base64, string fileName)
        //{
        //    var fileContents = Convert.FromBase64String(base64);

        //    return File(fileContents, contentType, fileName);
        //}
        #endregion
    }
}